package com.hsnn.medstgmini.settlement.dao.impl;

import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.settlement.dao.SettlementPsDao;
import com.hsnn.medstgmini.settlement.model.form.SetPaymentPayDetailForm;
import com.hsnn.medstgmini.settlement.service.SettlementPsManager;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;

@Repository
public class SettlementPsDaoImpl implements SettlementPsDao{

	@Autowired
	private HttpServletRequest request;
	@Autowired
	private SettlementPsManager settlementPsManager;
	@Autowired
	private HibernateSqlUtil dao;
	/**
	 * @category 获取配送企业查看结算单
	 * @Auther 徐国鹏
	 * @date 2015年7月29日 上午9:55:34
	 * @param page
	 * @return Pagination
	 */
	@Override
	public Pagination getSettlementPsList(Pagination page) {
		StringBuffer sql = new StringBuffer()
		.append(" select a.payment_bill_id as paymentBillId,max(c.payment_bill_code) as paymentBillCode,max(c.payment_bill_name) as paymentBillName, ")
		.append(" count(DISTINCT b.hosp_id) as hospCount,count(DISTINCT a.bank_acct_trans_info_id) as drugCount, ")
		.append(" sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney, ")
		.append(" DATE_FORMAT(max(a.pay_time),'%Y-%m-%d %T') as payTime,f.name as payUser from set_payment_paydetail a ")
		.append(" join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id ")
		.append(" join set_payment c on c.payment_bill_id=a.payment_bill_id ")
		.append(" left join sys_user f on f.id=a.pay_user ")
		.append(" where b.comp_id=:compId  and a.pay_status=1 ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", page.getConditions().get("compId"));
		SetPaymentPayDetailForm form = (SetPaymentPayDetailForm) page.getForm();
		if (StringUtils.isNotBlank(form.getPayTime())) {//支付时间
			Timestamp payTime = DateUtil.getTimeStamp(DateUtil.parseDateTime(form.getPayTime()));
			sql.append(" and a.pay_time > :payTime");
			params.put("payTime", payTime);
		}
		if(StringUtils.isNotBlank(form.getSubmitEndDatetime())){//查询截止时间
			Timestamp submitEndDatetime = DateUtil.getTimeStamp(DateUtil.parseDateTime(form.getSubmitEndDatetime()));
			sql.append(" and a.pay_time < :submitEndDatetime");
			params.put("submitEndDatetime", submitEndDatetime);
		}
		if (StringUtils.isNotBlank(form.getPaymentBillCode())){//支付单编号
			sql.append(" and c.payment_bill_code = :paymentBillCode ");
			params.put("paymentBillCode", form.getPaymentBillCode());
		}
		if (StringUtils.isNotBlank(form.getPaymentBillName())){//支付单名称
			sql.append(" and c.payment_bill_name like :paymentBillName ");
			params.put("paymentBillName", "%"+form.getPaymentBillName()+"%");
		}
		if (StringUtils.isNotBlank(form.getBankAcctTransType())) {// 支付类型
			sql.append(" and b.bank_acct_trans_type =:bankAcctTransType");
			params.put("bankAcctTransType", form.getBankAcctTransType());
		}
		sql.append(" group by a.payment_bill_id,f.name ");
		sql.append(" ORDER BY a.pay_time DESC ");	//列表按支付时间倒序排列
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * @category 点击医疗机构条目数获取医疗机构信息（配送企业端）
	 * @Auther 徐国鹏
	 * @date 2015年7月29日 下午1:33:30
	 * @param page
	 * @return Pagination
	 */
	@Override
	public Pagination getPsHospSettleList(Pagination page) {
		StringBuffer sql = new StringBuffer()
		.append(" select d.hosp_id as hospId,max(d.hosp_code) as hospCode,max(d.hosp_name) as hospName,max(e.area_name) as areaName, ")
		.append(" count(DISTINCT a.bank_acct_trans_info_id) as drugCount, ")
		.append(" sum(case b.bank_acct_trans_type when 1 then (b.pro_price*b.recp_count) when 2 then (-b.pro_price*b.recp_count) end) as summoney, ")
		.append(" DATE_FORMAT(max(a.pay_time),'%Y-%m-%d %T') as payTime,f.name as payUser, a.payment_bill_id as paymentBillId ")
		.append(" from set_payment_paydetail a ")
		.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id ")
		.append(" left join set_payment c on c.payment_bill_id=a.payment_bill_id ")
		.append(" left join std_hospinfo d on d.hosp_id=b.hosp_id ")
		.append(" left join dic_area e on e.area_id=d.area_id ")
		.append(" left join sys_user f on f.id=a.pay_user ")
		.append(" where b.comp_id=:compId and a.payment_bill_id=:paymentBillId ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		SetPaymentPayDetailForm form = (SetPaymentPayDetailForm) page.getForm();
		params.put("compId", page.getConditions().get("compId"));
		params.put("paymentBillId", page.getConditions().get("paymentBillId"));
		if(StringUtils.isNotBlank(form.getHospCode())){
			sql.append(" and d.hosp_code = :hospCode");
			params.put("hospCode", form.getHospCode());
		}
		if(StringUtils.isNotBlank(form.getHospName())){
			sql.append(" and d.hosp_name like :hospName");
			params.put("hospName", "%"+form.getHospName()+"%");
		}
		if (StringUtils.isNotBlank(form.getBankAcctTransType())) {// 支付类型x
			sql.append(" and b.bank_acct_trans_type =:bankAcctTransType");
			params.put("bankAcctTransType", form.getBankAcctTransType());
		}
		if (StringUtils.isNotBlank(page.getConditions().get("areaId"))) {// 支付类型x
			sql.append(" and d.area_id like :areaId ");
			params.put("areaId","%"+page.getConditions().get("areaId")+"%");
		}
		
		sql.append(" group by b.hosp_id,f.name ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	/**
	 * @category 点击药品条目数获取药品信息（配送企业端）
	 * @Auther 徐国鹏
	 * @date 2015年7月29日 下午1:48:06
	 * @param page
	 * @return Pagination
	 */
	@Override
	public Pagination getPsDrugSettleList(Pagination page) {
		StringBuffer sql = new StringBuffer()
		.append(" select b.bank_acct_trans_type as bankAcctTransType, ")
		.append(" d.drug_code as drugCode, d.drug_name as drugName,d.form_name as formName, ")
		.append(" d.spec_name as specName,d.factor as factor,d.unit as unit,d.material_name as materialName,d.comp_name as compName, ")
		.append(" b.pro_price as proPrice,sum(b.recp_count) as recpCount, ")
		.append(" sum(b.pro_price*b.recp_count) as summoney ")
		.append(" from set_payment_paydetail a ")
		.append(" left join set_settletdetailinfo b on a.bank_acct_trans_info_id=b.trans_id ")
		.append(" left join view_drug d on d.drug_id=b.drug_id ")
		.append(" where b.comp_id=:compId ")
		.append(" and a.payment_bill_id=:paymentBillId ")
		.append(" and b.hosp_id=:hospId ");
		Map<String, Object> params = new HashMap<String, Object>();
		SetPaymentPayDetailForm form = (SetPaymentPayDetailForm) page.getForm();
		params.put("compId", page.getConditions().get("compId"));
		params.put("paymentBillId", page.getConditions().get("paymentBillId"));
		params.put("hospId", page.getConditions().get("hospId"));
		if(StringUtils.isNotBlank(form.getDrugCode())){
			sql.append(" and d.drug_code =:drugCode");
			params.put("drugCode", form.getDrugCode());
		}
		if(StringUtils.isNotBlank(form.getDrugName())){
			sql.append(" and (d.drug_name like :drugName OR drug_name_spel like :drugName)");
			params.put("drugName", "%"+form.getDrugName()+"%");
		}
		if(StringUtils.isNotBlank(form.getCompName())){
			sql.append(" and d.comp_name =:compName");
			params.put("compName", form.getCompName());
		}
		sql.append(" GROUP BY d.drug_id");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	/**
	 * @category 点击药品条目数获取药品信息
	 * @Auther 周奇
	 * @date 2015年8月5日08:40:23
	 * @param page
	 * @return Pagination
	 */
	public Pagination getPsDrugSettleListForPayment(Pagination page){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT MAX(A.payment_bill_id) paymentBillId, C.drug_id drugId, MAX(C.drug_code) drugCode, MAX(C.prod_comp_id) compId,MAX(C.drug_name) drugName,")
		   .append(" MAX(C.spec_name) specName,MAX(C.factor) factor,MAX(C.unit) unit,MAX(C.material_name) materialName,MAX(C.comp_name) compName,")
		   .append(" sum(B.recp_count)  recpCount, MAX(B.pro_price)  proPrice, MAX(C.form_name) formName, SUM(B.recp_count * B.pro_price) summoney, count(DISTINCT B.hosp_id) hospCount, ")
		   .append(" B.bank_acct_trans_type as bankAcctTransType FROM set_payment_paydetail A JOIN set_settletdetailinfo B ON A.bank_acct_trans_info_id = B.trans_id")
		   .append(" JOIN view_drug C ON C.drug_id = B.drug_id ")
		   .append(" where 1 = 1");
		Map<String, Object> params = new HashMap<String, Object>();
	    Map<String, String> conditions =  page.getConditions();
	    if(null != conditions.get("drugCode")&& !"".equals(conditions.get("drugCode"))){
	    	sql.append(" and C.drug_code = :drugCode");
	    	String drugCode = conditions.get("drugCode");
	    	params.put("drugCode", drugCode);
	    }
	    if(null != conditions.get("drugName")&& !"".equals(conditions.get("drugName"))){
	    	sql.append(" and ( C.drug_name like :drugName OR C.drug_name_spel like :drugName)");
	    	String drugName = conditions.get("drugName");
	    	params.put("drugName", "%" + drugName + "%");
	    }
	    if(null != conditions.get("compId")&& !"".equals(conditions.get("compId"))){
	    	sql.append(" and B.comp_id = :compId");
	    	String compId = conditions.get("compId");
	    	params.put("compId", compId);
	    }
	    if(null != conditions.get("paymentBillId")&& !"".equals(conditions.get("paymentBillId"))){
	    	sql.append(" and A.payment_bill_id = :paymentBillId");
	    	String paymentBillId = conditions.get("paymentBillId");
	    	params.put("paymentBillId", paymentBillId);
	    }
	    sql.append(" GROUP BY c.drug_id,B.trans_id ");
	    return dao.getSqlPageMapList(sql.toString(), params, page);
		
	}
	/**
	 * @category 点击获取医疗机构列表
	 * @Auther 周奇
	 * @date 2015年8月5日14:29:27
	 * @param page
	 * @return Pagination
	 */
	public Pagination getPsHospSettleListForDrugpage(Pagination page) {
		StringBuffer sql = new StringBuffer()
		.append("SELECT MAX(C.hosp_code) as hospCode, MAX(C.hosp_name) as hospName, MAX(E.ar_shortname) as areaName,")
		.append(" sum(B.recp_count * B.pro_price) as summoney,sum(B.recp_count) as recpCount, D.name as payUser, DATE_FORMAT(max(A.pay_time),'%Y-%m-%d %T') as payTime")
		.append(" FROM set_payment_paydetail A JOIN set_settletdetailinfo B ON A.bank_acct_trans_info_id = B.trans_id")
		.append(" LEFT JOIN std_hospinfo C ON B.hosp_id = C.hosp_id left JOIN sys_user D")
		.append(" ON A.pay_user = D.id LEFT JOIN dic_area E ON C.area_id = E.area_id where 1 = 1");
		Map<String, Object> params = new HashMap<String, Object>();
		if(StringUtils.isNotBlank(page.getConditions().get("drugId"))){
			sql.append(" and B.drug_id = :drugId");
			params.put("drugId", page.getConditions().get("drugId"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("hospCode"))){
			sql.append(" and C.hosp_code = :hospCode");
			params.put("hospCode", page.getConditions().get("hospCode"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("hospName"))){
			sql.append(" and C.hosp_name like :hospName");
			params.put("hospName", "%" + page.getConditions().get("hospName")+"%");
		}
		if(StringUtils.isNotBlank(page.getConditions().get("paymentBillId"))){
			sql.append(" and A.payment_bill_id = :paymentBillId");
			params.put("paymentBillId", page.getConditions().get("paymentBillId"));
		}
		if(StringUtils.isNotBlank(page.getConditions().get("compId"))){
			sql.append(" and B.comp_id = :compId");
			params.put("compId", page.getConditions().get("compId"));
		}
		sql.append(" GROUP BY B.hosp_id ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

}
